package dao;

import entity.*;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import relationEntity.OrderTheme;
import relationEntity.OrderUser;
import relationEntity.PhotoTheme;
import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;

/**
 * Created by jifeng on 2017/2/9 0009.
 */
public class PhotographerDao {

    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    private Session getCurrentSession() {
        return sessionFactory.getCurrentSession();
    }

    public List<Photographer> findPhotographerList(){
        List<Photographer> photographerList=null;
        String sql="SELECT * from photographer";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Transformers.aliasToBean(Photographer.class));
        photographerList =query.list();
        return photographerList;
    }
    
    public void savePhotographer(Photographer photographer){
        getCurrentSession().saveOrUpdate(photographer);
    }

    public void saveTheme(Theme theme){
        getCurrentSession().save(theme);
    }

    public List<PhotoTheme> findPhotoTheme(){
        List<PhotoTheme> photoThemeList = null;
        String sql="SELECT \n" +
                "t.advertisingMap,\n" +
                "t.details,\n" +
                "t.price,\n" +
                "t.photo,\n" +
                "t.id,\n" +
                "t.themeName, \n" +
                "t.startDate,t.duration,t.negative,t.decoration,t.series,t.rankValue,\n" +
                "p.name,p.headPortrait\n" +
                " FROM theme t \n" +
                "LEFT JOIN photographer p ON p.id = t.photographerId where t.rankValue > 0 order by t.rankValue desc";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("name",StandardBasicTypes.STRING);
        query.addScalar("headPortrait",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("details",StandardBasicTypes.STRING);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("startDate",StandardBasicTypes.DATE);
        query.addScalar("duration",StandardBasicTypes.INTEGER);
        query.addScalar("negative",StandardBasicTypes.INTEGER);
        query.addScalar("decoration",StandardBasicTypes.INTEGER);
        query.addScalar("series",StandardBasicTypes.INTEGER);
        query.addScalar("rankValue",StandardBasicTypes.INTEGER);
        query.setResultTransformer(Transformers.aliasToBean(PhotoTheme.class));
        photoThemeList =query.list();
        return photoThemeList;
    }

    public Theme findThemeById(int id){
        List<Theme> themeList=null;
        String sql="SELECT * FROM theme WHERE theme.id="+id+"";
        SQLQuery query =getCurrentSession().createSQLQuery(sql).addEntity(Theme.class);
        themeList=query.list();
        return themeList.get(0);
    }
    
    public void deleteThemeById(int id){
        String sql="DELETE FROM theme WHERE theme.id="+id+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }
    
	public void updateTheme(Theme theme) {
		String sql = "UPDATE theme SET "
				+ " themeName = '" + theme.getThemeName() + "' " 
				+ ", price = "+ theme.getPrice() + " " 
				+ ", photographerId = " + theme.getPhotographerId() + " " 
				+ ", details = '"+ theme.getDetails() + "' " 
				+ ", advertisingMap = '" + theme.getAdvertisingMap() + "' " 
				+ ", duration = "+ theme.getDuration() + " " 
				+ ", negative = " + theme.getNegative()+ " " 
				+ ", decoration = "+ theme.getDecoration() + " " 
				+ ", series = " + theme.getSeries() + " " 
				+ ", rankValue = " + theme.getRankValue() + " " 
				+ "WHERE theme.id="+ theme.getId() + "";
		getCurrentSession().createSQLQuery(sql).executeUpdate();
	}
	
	public void updatePhotographer(Photographer photographer) {
		String sql = "UPDATE Photographer SET "
				+ " headPortrait = '" + photographer.getHeadPortrait() + "' " 
				+ ", name = '"+ photographer.getName() + "' " 
				+ ", appointment = " + photographer.getAppointment() + " " 
				+ ", briefintro = '"+ photographer.getBriefintro() + "' " 
				+ ", experience = '" + photographer.getExperience() + "' " 
				+ "WHERE id="+ photographer.getId() + "";
		getCurrentSession().createSQLQuery(sql).executeUpdate();
	}

    public List<PhotoTheme> findPhotoThemeById(int id){
        List<PhotoTheme> photoThemeList = null;
        String sql="SELECT \n" +
                "t.advertisingMap,\n" +
                "t.details,\n" +
                "t.price,\n" +
                "t.photo,\n" +
                "t.id,\n" +
                "t.themeName,\n" +
                "t.startDate,\n" +
                "p.name\n" +
                " FROM theme t \n" +
                "LEFT JOIN photographer p ON p.id = t.photographerId\n" +
                "WHERE p.id="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("name",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("details",StandardBasicTypes.STRING);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("startDate",StandardBasicTypes.DATE);
        query.setResultTransformer(Transformers.aliasToBean(PhotoTheme.class));
        photoThemeList =query.list();
        return photoThemeList;
    }

    public Photographer findPhotographerById(int id){
        List<Photographer> photographerList=null;
        String sql="SELECT * FROM photographer WHERE photographer.id="+id+"";
        SQLQuery query =getCurrentSession().createSQLQuery(sql).addEntity(Photographer.class);
        photographerList= query.list();
        return photographerList.get(0);
    }

    public List<Evaluate> findEvaluateByPhId(int id){
        List<Evaluate> evaluateList=null;
        String sql="SELECT * FROM evaluate WHERE evaluate.photographerId="+id+"";
        SQLQuery query =getCurrentSession().createSQLQuery(sql).addEntity(Evaluate.class);
        evaluateList=query.list();
        return evaluateList;
    }

    public void addPhotoorder(Photoorder photoorder){
        getCurrentSession().save(photoorder);
    }

    public void updatePhotoorder(String orderCode,int photoorderId){
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.orderCode="+orderCode+" WHERE photoorder.id ="+photoorderId+"").executeUpdate();
    }

    public List<OrderTheme> findOrderThemeByUserId(int id){
        List<OrderTheme> orderThemeList=null;
        String sql="SELECT\n" +
                "p.id,\n" +
                "p.shotDate,\n" +
                "t.advertisingMap,\n" +
                "t.themeName,\n" +
                "t.price,\n" +
                "t.id AS themeId,\n" +
                "t.photo,ph.name\n" +
                "FROM\n" +
                "photoorder p\n" +
                "LEFT JOIN theme AS t on t.id = p.themeId\n" 
                + " LEFT JOIN photographer ph ON t.photographerId = ph.id "+
                "WHERE p.userId="+id+" and p.sendStatus='待拍摄' and p.paymentStatus='已付款'";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("shotDate",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("themeId",StandardBasicTypes.INTEGER);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.addScalar("name",StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(OrderTheme.class));
        orderThemeList= query.list();
        return orderThemeList;
    }

    public OrderTheme findOrderThemeById(int id){
        List<OrderTheme> orderThemeList = null;
        String sql="SELECT\n" +
                "p.id,\n" +
                "p.shotDate,\n" +
                "t.advertisingMap,\n" +
                "t.themeName,\n" +
                "t.price,\n" +
                "t.id AS themeId,\n" +
                "t.photo,p.sendStatus,p.cloudAddress,p.extractedCode \n" +
                "FROM\n" +
                "photoorder p\n" +
                "LEFT JOIN theme AS t on t.id = p.themeId\n" +
                "WHERE p.id="+id+"\n" +
                "GROUP BY p.themeId";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("shotDate",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("themeId",StandardBasicTypes.INTEGER);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.addScalar("sendStatus",StandardBasicTypes.STRING);
        query.addScalar("cloudAddress",StandardBasicTypes.STRING);
        query.addScalar("extractedCode",StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(OrderTheme.class));
        orderThemeList= query.list();
        return orderThemeList.get(0);
    }

    public List<OrderTheme> findOrderThemeByUserID(int id){
        List<OrderTheme> orderThemeList=null;
        String sql="SELECT\n" +
                "p.id,\n" +
                "p.shotDate,\n" +
                "t.advertisingMap,\n" +
                "t.themeName,\n" +
                "t.price,\n" +
                "t.id AS themeId,\n" +
                "t.photo\n" +
                "FROM\n" +
                "photoorder p\n" +
                "LEFT JOIN theme AS t on t.id = p.themeId\n" +
                "WHERE p.userId="+id+" AND p.sendStatus='待评价'\n" +
                " GROUP BY p.themeId";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("shotDate",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("themeId",StandardBasicTypes.INTEGER);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(OrderTheme.class));
        orderThemeList= query.list();
        return orderThemeList;
    }

    public void addEvaluate(Evaluate evaluate){
        getCurrentSession().save(evaluate);
    }

    public void addAppreciate(Appreciate appreciate){
        getCurrentSession().save(appreciate);
    }

    public List<Appreciate> findAppreciateList(int themeId){
        List<Appreciate> appreciateList=null;
        appreciateList=getCurrentSession().createSQLQuery("SELECT * FROM appreciate WHERE appreciate.themeId="+themeId+"").addEntity(Appreciate.class).list();
        return appreciateList;
    }

    public List<Appreciate> findAppreciate(int themeId){
        List<Appreciate> appreciateList=null;
        appreciateList=getCurrentSession().createSQLQuery("SELECT * FROM appreciate WHERE appreciate.themeId="+themeId+" ").addEntity(Appreciate.class).list();
        return appreciateList;
    }

    public void deleteAppreciate(int themeId,int userId){
        getCurrentSession().createSQLQuery("DELETE FROM appreciate WHERE appreciate.themeId="+themeId+" AND appreciate.userId="+userId+"").executeUpdate();
    }
    
    public List<Appreciate> findAppreciateList(int themeId,int userId){
    	List<Appreciate> appreciateList=null;
        appreciateList=getCurrentSession().createSQLQuery("SELECT * FROM appreciate WHERE appreciate.themeId="+themeId+" AND appreciate.userId="+userId+"").addEntity(Appreciate.class).list();
        return appreciateList;
    }
    
    public void addAppreciate(int themeId,int userId){
    	getCurrentSession().createSQLQuery("INSERT INTO appreciate (themeId,userid) VALUE ('"+themeId+"','"+userId+"')").executeUpdate();
    	System.out.print("INSERT INTO appreciate (themeId,userid) VALUE ('"+themeId+"','"+userId+"')");
    }

    public void updatePHOrder(int orderId){
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.sendStatus='已评价' WHERE photoorder.id="+orderId+"").executeUpdate();
    }
    
    public void updatePHOrderStatus(int orderId,String orderStatus, String cloudAddress, String extractedCode){
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.sendStatus='" + orderStatus + 
                                     "',photoorder.cloudAddress='"+cloudAddress+"',photoorder.extractedCode='"+extractedCode+"' WHERE photoorder.id="+orderId+"").executeUpdate();
    }

    public List<Photoorder> findAppointment(String startDate,String endDate){
        List<Photoorder> photoorderList=null;
        String sql="SELECT\n" +
                "*\n" +
                "FROM\n" +
                "photoorder\n" +
                "WHERE photoorder.shotDate BETWEEN '"+startDate+"' AND '"+endDate+"'\n";
        photoorderList=getCurrentSession().createSQLQuery(sql).addEntity(Photoorder.class).list();
        return photoorderList;
    }
    
    public List<Photoorder> findScheduledHours(String scheduleDate){
        List<Photoorder> photoorderList=null;
        String sql="SELECT\n" +
                "*\n" +
                "FROM\n" +
                "photoorder\n" +
                "WHERE scheduleDate ='" + scheduleDate + "' AND paymentStatus = '已付款'";
        photoorderList=getCurrentSession().createSQLQuery(sql).addEntity(Photoorder.class).list();
        return photoorderList;
    }

    public int getPhotoOrderAllRowCount(String orderCode) {
        String sql ="SELECT\n" +
                "photoorder.id,\n" +
                "photoorder.startDate,\n" +
                "photoorder.orderCode,\n" +
                "photoorder.paymentTime,\n" +
                "photoorder.sendStatus,\n" +
                "photoorder.paymentType,\n" +
                "photoorder.totalMoney,\n" +
                "photoorder.themeId,\n" +
                "photoorder.userId,\n" +
                "photoorder.shotDate,\n" +
                "user.name";

        String exceptSelectSql =" FROM\n" +
                "photoorder\n" +
                "LEFT JOIN user ON user.id = photoorder.userId WHERE 1=1";

        if (orderCode!=null&orderCode!=""){
            exceptSelectSql+=" AND photoorder.orderCode LIKE '%"+orderCode+"%'";
        }
            exceptSelectSql+=" GROUP BY photoorder.shotDate DESC";
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public List<OrderUser> photoorderList(int offset,String orderCode, int length){
        List<OrderUser> orderUserList =null;
        try {
            String sql ="SELECT\n" +
                    "photoorder.id,\n" +
                    "photoorder.startDate,\n" +
                    "photoorder.orderCode,\n" +
                    "photoorder.paymentTime,\n" +
                    "photoorder.sendStatus,\n" +
                    "photoorder.paymentType,\n" +
                    "photoorder.totalMoney,\n" +
                    "photoorder.themeId,\n" +
                    "photoorder.userId,\n" +
                    "photoorder.shotDate,\n" +
                    "user.name,user.phone,ph.name as photographerName,t.themeName as themeTitle ";

            String exceptSelectSql =" FROM\n" +
                    "photoorder\n" +
                    "LEFT JOIN user ON user.id = photoorder.userId "
                    + " LEFT JOIN theme t ON photoorder.themeId = t.id "
                    + " LEFT JOIN photographer ph ON t.photographerId = ph.id ";

            if (orderCode!=null&orderCode!=""){
                exceptSelectSql+=" AND photoorder.orderCode LIKE '%"+orderCode+"%'";
            }
            exceptSelectSql+="  ORDER BY photoorder.orderCode DESC";
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("orderCode",StandardBasicTypes.STRING);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("sendStatus",StandardBasicTypes.STRING);
            query.addScalar("paymentType",StandardBasicTypes.STRING);
            query.addScalar("totalMoney",StandardBasicTypes.LONG);
            query.addScalar("themeId", StandardBasicTypes.INTEGER);
            query.addScalar("userId", StandardBasicTypes.INTEGER);
            query.addScalar("shotDate",StandardBasicTypes.STRING);
            query.addScalar("name",StandardBasicTypes.STRING);
            query.addScalar("phone",StandardBasicTypes.STRING);
            
            query.addScalar("photographerName",StandardBasicTypes.STRING);
            query.addScalar("themeTitle",StandardBasicTypes.STRING);
            
            query.setResultTransformer(Transformers.aliasToBean(OrderUser.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            orderUserList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return orderUserList;
    }

    public void addSample(Sample sample){
        getCurrentSession().createSQLQuery("INSERT INTO sample (userId,themeId,photoes,orderId) VALUE ('"+sample.getUserId()+"','"+sample.getThemeId()+"','"+sample.getPhotoes()+"',"+sample.getOrderId()+")").executeUpdate();
        System.out.print("INSERT INTO sample (userId,themeId,photoes) VALUE ('"+sample.getUserId()+"','"+sample.getThemeId()+"','"+sample.getPhotoes()+"')");
    }

    public void updatePhotoOrder(int orderId){
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.sendStatus='待选片' WHERE photoorder.id="+orderId+"").executeUpdate();

    }

    public void updateSamplOrder(int orderId){
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.sendStatus='待精修',userConfirmDate='"+sdf.format(date)+"' WHERE photoorder.id="+orderId+"").executeUpdate();

    }

    public List<OrderTheme> findOrderByStatus(int id){
        List<OrderTheme> orderThemeList=null;
        String sql="SELECT\n" +
                "p.id,p.sendStatus AS sendStatus,\n" +
                "p.shotDate,p.userConfirmDate AS userConfirmDate,\n" +
                "t.advertisingMap,\n" +
                "t.themeName,\n" +
                "t.price,\n" +
                "t.id AS themeId,\n" +
                "t.photo\n" +
                "FROM\n" +
                "photoorder p\n" +
                "LEFT JOIN theme AS t on t.id = p.themeId\n" +
                "WHERE p.userId="+id+" AND p.sendStatus IN ('已拍摄','待选片') " +
                " GROUP BY p.themeId";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("shotDate",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("sendStatus",StandardBasicTypes.STRING);
        query.addScalar("userConfirmDate",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("themeId",StandardBasicTypes.INTEGER);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(OrderTheme.class));
        orderThemeList= query.list();
        return orderThemeList;
    }
    
    public List<OrderTheme> findOrderByStatus(int id,String status){
        List<OrderTheme> orderThemeList=null;
        String sql="SELECT\n" +
                "p.id,p.sendStatus AS sendStatus,\n" +
                "p.shotDate,p.userConfirmDate AS userConfirmDate,\n" +
                "t.advertisingMap,\n" +
                "t.themeName,\n" +
                "t.price,\n" +
                "t.id AS themeId,\n" +
                "t.photo,ph.name \n" +
                "FROM\n" +
                "photoorder p\n" +
                "LEFT JOIN theme AS t on t.id = p.themeId \n" +
                "LEFT JOIN photographer AS ph on t.photographerid = ph.id  \n" +
                "WHERE p.userId="+id+" AND p.sendStatus in ("+status+") " +
                " ";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id", StandardBasicTypes.INTEGER);
        query.addScalar("shotDate",StandardBasicTypes.STRING);
        query.addScalar("advertisingMap",StandardBasicTypes.STRING);
        query.addScalar("themeName",StandardBasicTypes.STRING);
        query.addScalar("sendStatus",StandardBasicTypes.STRING);
        query.addScalar("userConfirmDate",StandardBasicTypes.STRING);
        query.addScalar("price",StandardBasicTypes.LONG);
        query.addScalar("themeId",StandardBasicTypes.INTEGER);
        query.addScalar("photo",StandardBasicTypes.STRING);
        query.addScalar("name",StandardBasicTypes.STRING);
        query.setResultTransformer(Transformers.aliasToBean(OrderTheme.class));
        orderThemeList= query.list();
        return orderThemeList;
    }

    public List<Sample> findSampleList(int themeId,int userId,int orderId){
        List<Sample> sampleList=null;
        sampleList=getCurrentSession().createSQLQuery("SELECT * FROM sample WHERE sample.userId="+userId+" AND sample.themeId="+themeId+" AND sample.orderId="+orderId+"").addEntity(Sample.class).list();
        return sampleList;
    }

    public List<Sample> findSampleByids(String ids){
        List<Sample> sampleList=null;
        sampleList=getCurrentSession().createSQLQuery("SELECT * FROM sample WHERE sample.id in("+ids+")").addEntity(Sample.class).list();
        return sampleList;
    }
    
    public void updateSampleByids(String ids){
        getCurrentSession().createSQLQuery("UPDATE sample set selectFlag = 1 where sample.id in("+ids+")").executeUpdate();
    }

    public List<Sample> findSampleNotByIds(int themeId,int userId,String ids){
        List<Sample> sampleList=null;
        sampleList=getCurrentSession().createSQLQuery("SELECT * FROM sample WHERE sample.userId="+userId+" AND sample.themeId="+themeId+" AND sample.id NOT IN("+ids+")").addEntity(Sample.class).list();
        System.out.print("SELECT * FROM sample WHERE sample.userId="+userId+" AND sample.themeId="+themeId+" AND sample.id NOT IN("+ids+")");
        return sampleList;
    }

    public void addSelectPhoto(SelectPhoto selectPhoto){
        getCurrentSession().save(selectPhoto);
    }

    public List<OrderUser> findOrderByStatus(int offset,String orderCode,int length){
        List<OrderUser> orderUserList =null;
        try {
            String sql ="SELECT\n" +
                    "photoorder.id,\n" +
                    "photoorder.startDate,\n" +
                    "photoorder.orderCode,\n" +
                    "photoorder.paymentTime,\n" +
                    "photoorder.sendStatus,\n" +
                    "photoorder.paymentType,\n" +
                    "photoorder.totalMoney,\n" +
                    "photoorder.themeId,\n" +
                    "photoorder.userId,\n" +
                    "photoorder.shotDate,\n" +
                    "user.name";

            String exceptSelectSql =" FROM\n" +
                    "photoorder\n" +
                    "LEFT JOIN user ON user.id = photoorder.userId WHERE  photoorder.sendStatus='待精修'";

            if (orderCode!=null&orderCode!=""){
                exceptSelectSql+=" AND photoorder.orderCode LIKE '%"+orderCode+"%'";
            }
            exceptSelectSql+=" GROUP BY photoorder.shotDate DESC";
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("orderCode",StandardBasicTypes.STRING);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("sendStatus",StandardBasicTypes.STRING);
            query.addScalar("paymentType",StandardBasicTypes.STRING);
            query.addScalar("totalMoney",StandardBasicTypes.LONG);
            query.addScalar("themeId", StandardBasicTypes.INTEGER);
            query.addScalar("userId", StandardBasicTypes.INTEGER);
            query.addScalar("shotDate",StandardBasicTypes.STRING);
            query.addScalar("name",StandardBasicTypes.STRING);
            query.setResultTransformer(Transformers.aliasToBean(OrderUser.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            orderUserList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return orderUserList;
    }


    public int findOrderByStatusAllRowCount(String orderCode) {
        String sql ="SELECT\n" +
                "photoorder.id,\n" +
                "photoorder.startDate,\n" +
                "photoorder.orderCode,\n" +
                "photoorder.paymentTime,\n" +
                "photoorder.sendStatus,\n" +
                "photoorder.paymentType,\n" +
                "photoorder.totalMoney,\n" +
                "photoorder.themeId,\n" +
                "photoorder.userId,\n" +
                "photoorder.shotDate,\n" +
                "user.name";

        String exceptSelectSql =" FROM\n" +
                "photoorder\n" +
                "LEFT JOIN user ON user.id = photoorder.userId WHERE photoorder.sendStatus='待精修'";

        if (orderCode!=null&orderCode!=""){
            exceptSelectSql+=" AND photoorder.orderCode LIKE '%"+orderCode+"%'";
        }
        exceptSelectSql+=" GROUP BY photoorder.shotDate DESC";
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public List<SelectPhoto> findSelectPhotoByOrderId(int orderId){
        return getCurrentSession().createSQLQuery("SELECT * FROM selectPhoto WHERE selectPhoto.orderId="+orderId+"").addEntity(SelectPhoto.class).list();
    }

    public List<Sample> findSampleByIdList(String ids){
        return getCurrentSession().createSQLQuery("SELECT * FROM sample WHERE sample.id IN ("+ids+")").addEntity(Sample.class).list();
    }

    public void updateOrder(String orderCode){
        getCurrentSession().createSQLQuery("UPDATE photoorder SET photoorder.sendStatus='待拍摄',photoorder.paymentStatus='已付款' WHERE photoorder.orderCode='"+orderCode+"'").executeUpdate();

    }
    public List<Photographer> selPhotographer() {
        String sql = "select * from photographer order by id";
		return getCurrentSession().createSQLQuery(sql).addEntity(Photographer.class).list();
	}
	public List<String> selPotOr(int id) {
		
		 String sql = "select  por.scheduleDate from photographer pg,photoorder por,theme t\n"+
				      "where pg.id = t.photographerId\n"+
				      "and   por.themeId = t.id \n"+
				      "and   pg.id = "+id+
				      "\nGROUP BY pg.id,por.scheduleDate";
		return getCurrentSession().createSQLQuery(sql).list();
	}
	
	public void addSchedule(String id, String schedleDate) {
		 getCurrentSession().createSQLQuery("INSERT INTO scheduleclose (photographerId,scheduleDate) VALUE ('"+id+"','"+schedleDate+"')").executeUpdate();
	}
	public List<String> selPotCl(int id) {
		
		 String sql = "SELECT  t.scheduleDate FROM scheduleclose t\n"+
			      "WHERE t.photographerId = "+id+
			      "\nGROUP BY t.photographerId,t.scheduleDate";
	return getCurrentSession().createSQLQuery(sql).list();
	}
	
	public List<Integer> selLi(String id, String schedleDate) {
		 String sql = "SELECT  t.id FROM scheduleclose t\n"+
			      "WHERE t.photographerId = "+id+
			       "\nAND t.scheduleDate='"+schedleDate+"'\nGROUP BY t.photographerId,t.scheduleDate";
	return getCurrentSession().createSQLQuery(sql).list();
	}
	public void delSchedule(String id, String schedleDate) {
		 String sql = "DELETE FROM scheduleclose \n"+
			      "WHERE photographerId = "+id+
			       "\nAND scheduleDate='"+schedleDate+"'";
	     getCurrentSession().createSQLQuery(sql).executeUpdate();
	}
}
